const mysql = require('mysql')
const config = require('./config')
const debug = require('../utils/constant')
const e = require('express')

// 定义Mysql数据库的连接方法
function connect() {    // 数据库的连接
  return mysql.createConnection({
    host: config.host,
    user: config.user,
    password: config.password,
    database: config.database,
    port: config.port,
    multipleStatements: true
  })
}

//  定义一个查询数据库用的方法，括号里输入查询语句
function querySql(sql) {
  const conn = connect()
  // debug && console.log(sql)
  return new Promise((resolve, reject) => {
    try {
      conn.query(sql, (err, result) => {
        if (err) {
          debug && console.log('查询失败，原因:' + JSON.stringify(err))
          reject(err)
        } else {
          debug && console.log('查询成功', JSON.stringify(result))
          resolve(result)
        }
      })
    } catch (err) {
      reject(err)
    } finally {
      conn.end()
    }
  })
}

// 定义一个只查询一条数据的查询方法
function queryOne(sql) {
  return new Promise((resolve, reject) => {
    querySql(sql).then((result) => {
      if (result && result.length > 0) {
        resolve(result[0])
      } else {
        reject(null)
      }
    }).catch((err) => {
      reject(err)
    })
  })
}

// 定义删除数据的方法
function deleteSql(tag, tagContent, tableName) {
  const conn = connect()
  const sql = `delete from ${tableName} where ${tag} = ${tagContent}`
  return new Promise((resolve, reject) => {
    try {
      conn.query(sql, (err, result) => {
        if (err) {
          debug && console.log('删除失败，原因:' + JSON.stringify(err))
          reject(err)
        } else {
          debug && console.log('删除成功', JSON.stringify(result))
          resolve(result)
        }
      })
    } catch (err) {
      reject(err)
    } finally {
      conn.end()
    }
  })
}

// 定义删除多个数据的方法
function deleteManySql(tag, tagContents, tableName) {
  const conn = connect()
  const sql = `delete from ${tableName} where ${tag} in (${tagContents.join(',')})`
  return new Promise((resolve, reject) => {
    try {
      conn.query(sql, (err, result) => {
        if (err) {
          debug && console.log('删除失败，原因:' + JSON.stringify(err))
          reject(err)
        } else {
          debug && console.log('删除成功', JSON.stringify(result))
          resolve(result)
        }
      })
    } catch (err) {
      reject(err)
    } finally {
      conn.end()
    }
  })
}

// 定义插入数据的方法
function insertSql(model, tableName) {
  return new Promise((resolve, reject) => {
    const keys = []
    const values = []
    Object.keys(model).forEach(key => {
      if (model.hasOwnProperty(key)) {
        keys.push(`\`${key}\``)
        values.push(`'${model[key]}'`)
      }
    })
    if (keys.length > 0 && values.length > 0) {
      let sql = `INSERT INTO \`${tableName}\`(`
      const keysString = keys.join(',')
      const valuesString = values.join(',')
      sql = `${sql}${keysString}) VALUES (${valuesString})`
      const conn = connect()
      try {
        conn.query(sql, (err, result) => {
          if (err) {
            reject(err)
          } else {
            resolve(result)
          }
        })
      } catch (e) {
        reject(e)
      } finally {
        conn.end()
      }
    } else {
      reject(new Error('SQL解析失败'))
    }

  })
}

// 定义更新数据的方法
function updateSql(model, tableName, where) {
  return new Promise(((resolve, reject) => {
    const entry = []
    Object.keys(model).forEach(key => {
      if (model.hasOwnProperty(key)&&model[key]!==undefined&&model[key]!==null) {
        entry.push(`\`${key}\`='${model[key]}'`)
      }
    })
    if (entry.length > 0) {
      let sql = `UPDATE \`${tableName}\` SET`
      sql = `${sql} ${entry.join(',')} ${where}`
      console.log(sql)
      const conn = connect()
      try {
        conn.query(sql, (err, result) => {
          if (err) {
            reject(err)
          } else {
            resolve(result)
          }
        })
      } catch (e) {
        reject(e)
      } finally {
        conn.end()
      }
    } else {
      reject(new Error('SQL解析失败'))
    }

  }))
}

module.exports = {
  querySql,
  queryOne,
  deleteSql,
  insertSql,
  updateSql,
  deleteManySql
}